package com.hkts.naturalgas.base.controller;


import com.alibaba.fastjson.JSON;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.toolkit.Wrappers;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.hkts.naturalgas.base.entity.*;
import com.hkts.naturalgas.base.service.*;
import com.hkts.naturalgas.utils.*;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.omg.CORBA.Object;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.bind.annotation.*;

import java.io.File;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.time.LocalDateTime;
import java.time.ZoneId;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * 煅烧脱硫脱硝
 *
 * @author yck
 * @since 2021-05-31
 */
@RestController
@RequestMapping("/instrumentdata/calcinationDesulfurizationDenitration")
public class CalcinationDesulfurizationDenitrationController {
    private final Logger logger = LoggerFactory.getLogger(CalcinationDesulfurizationDenitrationController.class);

    @Autowired
    public ICalcinationDesulfurizationDenitrationService iCalcinationDesulfurizationDenitrationService;
    @Autowired
    public IRoastingPurifyWetElectricityService iRoastingPurifyWetElectricityService;
    @Autowired
    public IRoastingPurificationBleachingService iRoastingPurificationBleachingService;
    @Autowired
    public ICalcinationPurifyWetElectricityService iCalcinationPurifyWetElectricityService;
    @Autowired
    public ICalcinationPurificationBleachingService iCalcinationPurificationBleachingService;
    @Autowired
    public IRoastingDustService iRoastingDustService;
    @Autowired
    public IOzoneService iOzoneService;
    @Autowired
    public IRoastingDesulphurizingService iRoastingDesulphurizingService;

    @Autowired
    public IHktsTailGasService iHktsTailGasService;

    /**
     * 分页查询数据
     *
     * @param page                                  分页信息
     * @param calcinationDesulfurizationDenitration 查询条件
     * @return
     */
    @ResponseBody
    @GetMapping("/getCalcinationDesulfurizationDenitrationPageList")
    public Result<CustomPage> getHontUserList(Page<CalcinationDesulfurizationDenitration> page, CalcinationDesulfurizationDenitration calcinationDesulfurizationDenitration) {
        return ResultUtil.data(CustomPageUtils.getCustomPage(iCalcinationDesulfurizationDenitrationService.selectPage(page, calcinationDesulfurizationDenitration)));
    }

    /**
     * 保存和修改公用的
     *
     * @param calcinationDesulfurizationDenitration 传递的实体
     * @return saveOrUpdate
     */
    @ResponseBody
    @RequestMapping(method = RequestMethod.POST, value = "/calcinationDesulfurizationDenitrationSave")
    public Result<Object> calcinationDesulfurizationDenitrationSave(CalcinationDesulfurizationDenitration calcinationDesulfurizationDenitration) {
        iCalcinationDesulfurizationDenitrationService.saveOrUpdate(calcinationDesulfurizationDenitration);
        return ResultUtil.success("保存成功");
    }

    /**
     * 根据id获取对象
     *
     * @param id 实体ID
     * @return 0 失败  1 成功
     */
    @ResponseBody
    @RequestMapping(method = RequestMethod.GET, value = "/calcinationDesulfurizationDenitrationGetById")
    public Result<CalcinationDesulfurizationDenitration> calcinationDesulfurizationDenitrationGetById(String id) {
        CalcinationDesulfurizationDenitration byId = iCalcinationDesulfurizationDenitrationService.getById(id);
        return ResultUtil.data(byId);
    }

    /**
     * 根据id删除对象
     *
     * @param id 实体ID
     * @return 0 失败  1 成功
     */
    @ResponseBody
    @RequestMapping(method = RequestMethod.POST, value = "/calcinationDesulfurizationDenitrationDelete")
    public Result<Object> hontUserDelete(String id) {
        iCalcinationDesulfurizationDenitrationService.removeById(id);
        return ResultUtil.success("删除成功");
    }

    /**
     * 批量删除对象
     *
     * @param item 实体集合ID
     * @return 0 失败  1 成功
     */
    @ResponseBody
    @RequestMapping(method = RequestMethod.POST, value = "/calcinationDesulfurizationDenitrationBatchDelete")
    public Result<Object> deleteBatchIds(String item) {
        List<String> ids = (List<String>) JSON.parse(item);
        iCalcinationDesulfurizationDenitrationService.removeByIds(ids);
        return ResultUtil.success("删除成功");
    }
    @GetMapping("/getPageByTime")
    public Result<CustomPage> getPageByTime(Page<CalcinationDesulfurizationDenitration> page, String startDate, String endDate){
        return ResultUtil.data(CustomPageUtils.getCustomPage(iCalcinationDesulfurizationDenitrationService.selectPageByTime(page,startDate,endDate)));
    }

    /**
     * 功能描述:
     * 测试连接sqlserver
     *
     * @return: com.hkts.naturalgas.utils.Result<java.lang.Object>
     * @Author: 杨春科
     * @Date: 2021-05-31 16:07
     * @Description //TODO
     */
    @ResponseBody
    @RequestMapping(method = RequestMethod.POST, value = "/handlerSqlServerData")
    public Result<Object> handlerSqlServerData() throws Exception {

        Connection connection = null;
        String driverName = "com.microsoft.sqlserver.jdbc.SQLServerDriver";//SQL数据库引擎
        String dbURL = "jdbc:sqlserver://192.168.4.4:1433;DatabaseName=data1";//数据源  ！！！注意若出现加载或者连接数据库失败一般是这里出现问题
        String Name = "sa";
        String Pwd = "hkts@1234";
        Class.forName(driverName);
        connection = DriverManager.getConnection(dbURL, Name, Pwd);
        PreparedStatement preparedStatement = connection.prepareStatement("select * from HKSJFW where CONVERT(datetime, 录入时间, 20) >'2021-06-10 10:16:48' order by CONVERT(datetime, 录入时间, 20)");
        ResultSet resultSet = preparedStatement.executeQuery();
        String dateFormate = "2021-06-10 10:16:48";
        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        SimpleDateFormat format2 = new SimpleDateFormat("yyyy/M/dd HH:mm:ss");
        SimpleDateFormat format3 = new SimpleDateFormat("yyyy/M/dd");
        Date date = format.parse(dateFormate);

        /** 功能描述:
         *[]
         * @return: com.hkts.naturalgas.utils.Result<java.lang.Object>
         * @Author: 杨春科
         * @Date: 2021-05-31 16:46
         * @Description //TODO
         */
        int index = 0;
        while (resultSet.next()) {

            LocalDateTime ldt =null;
            //时间处理
            String timeStr = resultSet.getString("录入时间").trim();
            if(timeStr.length()==18){
                Date parse = format2.parse(timeStr);
                 ldt = parse.toInstant()
                        .atZone(ZoneId.systemDefault())
                        .toLocalDateTime();
            }else{
                Date parse = format3.parse(timeStr);
               ldt = parse.toInstant()
                        .atZone(ZoneId.systemDefault())
                        .toLocalDateTime();
            }
            // 焙烧除尘
            RoastingDust dust = new RoastingDust();
            String id = String.valueOf(SnowFlakeUtil.getFlowIdInstance().nextId());
            dust.setId(id);
            dust.setTime(ldt);
            dust.setBsjhAi1(resultSet.getFloat("BS_JH_CC_AI1_1"));
            dust.setBsjhAi2(resultSet.getFloat("BS_JH_CC_AI1_2"));
            dust.setBsjhHvc240067(resultSet.getFloat("BS_JH_CC_HVC240067"));
            dust.setBsjhHvc140068(resultSet.getFloat("BS_JH_CC_HVC140068"));
            dust.setBsjhHvc140067(resultSet.getFloat("BS_JH_CC_HVC140067"));
            dust.setBsjhHvc240068(resultSet.getFloat("BS_JH_CC_HVC240068"));
            dust.setBsjhHvc340067(resultSet.getFloat("BS_JH_CC_HVC340067"));
            dust.setBsjhHvc340068(resultSet.getFloat("BS_JH_CC_HVC340068"));
            dust.setBsjhHvc440067(resultSet.getFloat("BS_JH_CC_HVC440067"));
            dust.setBsjhHvc440068(resultSet.getFloat("BS_JH_CC_HVC440068"));
            dust.setBsjhHvc540067(resultSet.getFloat("BS_JH_CC_HVC540067"));
            dust.setBsjhHvc540068(resultSet.getFloat("BS_JH_CC_HVC540068"));
            dust.setBsjhHvc640067(resultSet.getFloat("BS_JH_CC_HVC640067"));
            dust.setBsjhHvc640068(resultSet.getFloat("BS_JH_CC_HVC640068"));

            //焙烧脱硫
            RoastingDesulphurizing roastingDesulphurizing = new RoastingDesulphurizing();
            roastingDesulphurizing.setId(id);
            roastingDesulphurizing.setTime(ldt);
            roastingDesulphurizing.setDataFc(resultSet.getFloat("Data_block_1_FC_OUT"));//
            roastingDesulphurizing.setDataLt(resultSet.getFloat("Data_block_1_LT1001"));
            roastingDesulphurizing.setDataNd(resultSet.getFloat("Data_block_1_ND1"));
            roastingDesulphurizing.setDataNox(resultSet.getFloat("Data_block_1_NXO_OUT"));
            roastingDesulphurizing.setDataFt(resultSet.getFloat("Data_block_1_FT_OUT"));
            roastingDesulphurizing.setDataO2(resultSet.getFloat("Data_block_1_O2_OUT"));
            roastingDesulphurizing.setDataPh(resultSet.getFloat("Data_block_1_PH1"));
            roastingDesulphurizing.setDataPt(resultSet.getFloat("Data_block_1_PT_OUT"));
            roastingDesulphurizing.setDataPt1003(resultSet.getFloat("Data_block_1_PT1003"));
            roastingDesulphurizing.setDataSo2(resultSet.getFloat("Data_block_1_SO2_OUT"));
            roastingDesulphurizing.setDataTe(resultSet.getFloat("Data_block_1_TE_OUT"));
            roastingDesulphurizing.setDataTe1002(resultSet.getFloat("Data_block_1_TE1002"));
            roastingDesulphurizing.setDataTe1003(resultSet.getFloat("Data_block_1_TE1003"));

            // 焙烧净化湿电
            RoastingPurifyWetElectricity roastingPurifyWetElectricity = new RoastingPurifyWetElectricity();
            roastingPurifyWetElectricity.setId(id);
            roastingPurifyWetElectricity.setTime(ldt);
            roastingPurifyWetElectricity.setBsjhSd2i2(resultSet.getFloat("BS_JH_SD1_GY1_I2"));
            roastingPurifyWetElectricity.setBsjhSd2v2(resultSet.getFloat("BS_JH_SD1_GY1_V2"));
            roastingPurifyWetElectricity.setBsjhSdi2(resultSet.getFloat("BS_JH_SD2_GY2_I2"));
            roastingPurifyWetElectricity.setBsjhSdv2(resultSet.getFloat("BS_JH_SD2_GY2_V2"));

            //焙烧净化脱白
            RoastingPurificationBleaching roastingPurificationBleaching = new RoastingPurificationBleaching();
            roastingPurificationBleaching.setId(id);
            roastingPurificationBleaching.setTime(ldt);
            roastingPurificationBleaching.setBsjhFan1dl(resultSet.getFloat("BS_JH_TB2_FAN_1_DL"));
            roastingPurificationBleaching.setBsjhFan1sv(resultSet.getFloat("BS_JH_TB2_FAN_1_SV"));
            roastingPurificationBleaching.setBsjhSidu(resultSet.getFloat("BS_JH_TB2_SIDU"));
            roastingPurificationBleaching.setBsjhFan2dl(resultSet.getFloat("BS_JH_TB2_FAN_2_DL"));
            roastingPurificationBleaching.setBsjhFan2sv(resultSet.getFloat("BS_JH_TB2_FAN_2_SV"));
            roastingPurificationBleaching.setBsjhSidu2(resultSet.getFloat("BS_JH_TB2_SIDU_2"));
            roastingPurificationBleaching.setBsjhTe102(resultSet.getFloat("BS_JH_TB2_TE102"));
            roastingPurificationBleaching.setBsjhTe105(resultSet.getFloat("BS_JH_TB2_TE105"));

            //煅烧净化湿电
            CalcinationPurifyWetElectricity calcinationPurifyWetElectricity = new CalcinationPurifyWetElectricity();
            calcinationPurifyWetElectricity.setId(id);
            calcinationPurifyWetElectricity.setTime(ldt);
            calcinationPurifyWetElectricity.setDsjhSda1(resultSet.getFloat("DS_JH_SD2_一电场二次电流"));
            calcinationPurifyWetElectricity.setDsjhSda2(resultSet.getFloat("DS_JH_SD2_A二次电压"));
            calcinationPurifyWetElectricity.setDsjhSdb1(resultSet.getFloat("DS_JH_SD3_二电场二次电流"));
            calcinationPurifyWetElectricity.setDsjhSdb2(resultSet.getFloat("DS_JH_SD3_B二次电压"));

            //煅烧净化脱白
            CalcinationPurificationBleaching calcinationPurificationBleaching=new CalcinationPurificationBleaching();
            calcinationPurificationBleaching.setDsjhFanadl(resultSet.getFloat("DS_JH_TB_FAN_A_DL"));
            calcinationPurificationBleaching.setDsjhFanasv(resultSet.getFloat("DS_JH_TB_FAN_A_SV"));
            calcinationPurificationBleaching.setDsjhFanbdl(resultSet.getFloat("DS_JH_TB_FAN_B_DL"));
            calcinationPurificationBleaching.setDsjhFanbsv(resultSet.getFloat("DS_JH_TB_FAN_B_SV"));
            calcinationPurificationBleaching.setDsjhFancdl(resultSet.getFloat("DS_JH_TB_FAN_C_DL"));
            calcinationPurificationBleaching.setDsjhFancsv(resultSet.getFloat("DS_JH_TB_FAN_C_SV"));
            calcinationPurificationBleaching.setDsjhSidu(resultSet.getFloat("DS_JH_TB_SIDU"));
            calcinationPurificationBleaching.setDsjhTe103(resultSet.getFloat("DS_JH_TB_TE103"));
            calcinationPurificationBleaching.setDsjhTe108(resultSet.getFloat("DS_JH_TB_TE108"));
            calcinationPurificationBleaching.setDsjhTe110(resultSet.getFloat("DS_JH_TB_TE110"));
            calcinationPurificationBleaching.setId(id);
            calcinationPurificationBleaching.setTime(ldt);

            //煅烧脱硫脱硝
            CalcinationDesulfurizationDenitration calcinationDesulfurizationDenitration=new CalcinationDesulfurizationDenitration();
            // 目前系统没有的四个参数
            calcinationDesulfurizationDenitration.setDataFc(resultSet.getFloat("Data_block_1_FC_OUT"));
            calcinationDesulfurizationDenitration.setDataNox(resultSet.getFloat("Data_block_1_NXO_OUT"));
            calcinationDesulfurizationDenitration.setDataSo2(resultSet.getFloat("Data_block_1_SO2_OUT"));
            calcinationDesulfurizationDenitration.setDataO2(resultSet.getFloat("Data_block_1_O2_OUT"));

            calcinationDesulfurizationDenitration.setDsjhFt101(resultSet.getFloat("DS_JH_TX_FT101"));
            calcinationDesulfurizationDenitration.setDsjhFt102(resultSet.getFloat("DS_JH_TX_FT102"));
            calcinationDesulfurizationDenitration.setDsjhP111(resultSet.getFloat("DS_JH_TX_P111"));
            calcinationDesulfurizationDenitration.setDsjhP121(resultSet.getFloat("DS_JH_TX_P121"));
            calcinationDesulfurizationDenitration.setDsjhT111(resultSet.getFloat("DS_JH_TX_T111"));
            calcinationDesulfurizationDenitration.setDsjhTllt(resultSet.getFloat("DS_JH_TL_LT_101_1"));
            calcinationDesulfurizationDenitration.setDsjhT121(resultSet.getFloat("DS_JH_TX_T121"));
            calcinationDesulfurizationDenitration.setDsjhTlmd(resultSet.getFloat("DS_JH_TL_MD_102"));
            calcinationDesulfurizationDenitration.setDsjhTlph(resultSet.getFloat("DS_JH_TL_PH_101"));
            calcinationDesulfurizationDenitration.setId(id);
            calcinationDesulfurizationDenitration.setTime(ldt);

           //臭氧
            Ozone ozone=new Ozone();
            ozone.setBsjhCy1(resultSet.getFloat("BS_JH_CY1_出气流量"));
            ozone.setBsjhCy1c(resultSet.getFloat("BS_JH_CY1_臭氧浓度"));
            ozone.setBsjhCy2c(resultSet.getFloat("BS_JH_CY2_臭氧浓度"));
            ozone.setBsjhCy2f(resultSet.getFloat("BS_JH_CY2_出气流量"));
            ozone.setId(id);
            ozone.setTime(ldt);
            /////
            iRoastingDustService.save(dust);
            iRoastingDesulphurizingService.save(roastingDesulphurizing);
            iRoastingPurifyWetElectricityService.save(roastingPurifyWetElectricity);
            iRoastingPurificationBleachingService.save(roastingPurificationBleaching);
            iCalcinationPurifyWetElectricityService.save(calcinationPurifyWetElectricity);
            iCalcinationPurificationBleachingService.save(calcinationPurificationBleaching);
            iCalcinationDesulfurizationDenitrationService.save(calcinationDesulfurizationDenitration);
            iOzoneService.save(ozone);
            // 下标加一
            index++;
        }
        System.out.println("连接数据库成功");
        connection.close();
        return ResultUtil.success("连接成功！");
    }
    /* 功能描述:
    * 处理  工控系统缺失的数据
    * @return:
    * @Author: 杨春科
    * @Date: 2021-06-01 14:31
    * @Description //TODO
    */
    @ResponseBody
    @RequestMapping(method = RequestMethod.POST, value = "/dataHandler")
    public  Result dataHandler(){
        iCalcinationDesulfurizationDenitrationService.dataHandler();
        return ResultUtil.success("处理成功！");
    }
    /* 功能描述:
    *通过导入文件so2 nox o2 fc 等数据修改
    * @return: 
    * @Author: 杨春科
    * @Date: 2021-06-13 10:31
    * @Description //TODO 
    */
    @ResponseBody
    @RequestMapping(method = RequestMethod.POST, value = "/importDataUpdate")
    public Result<Object> importDataUpdate(String url) throws Exception {
        File excelFile = new File(url);
        HSSFWorkbook wb = new HSSFWorkbook(new FileInputStream(excelFile));
        HSSFSheet sheet = wb.getSheetAt(0);
        HSSFRow row = sheet.getRow(2);

       List<CalcinationDesulfurizationDenitration> list=new ArrayList();
        for (int i=5 ; i <sheet.getLastRowNum()-3; i++) {

            HSSFRow row1 = sheet.getRow(i);
            Date dateCellValue = row1.getCell(0).getDateCellValue();
            System.out.println(dateCellValue);
            row1.getCell(0).setCellType(CellType.STRING);
            String time=row1.getCell(0).getStringCellValue();
            QueryWrapper<CalcinationDesulfurizationDenitration> query = Wrappers.query();
            query.eq(CalcinationPurifyWetElectricity.TIME,dateCellValue.toInstant()
                    .atZone( ZoneId.systemDefault() )
                    .toLocalDateTime());

            CalcinationDesulfurizationDenitration one = iCalcinationDesulfurizationDenitrationService.getOne(query);
            if(one==null){
                continue;
            }
            row1.getCell(1).setCellType(CellType.STRING);
            one.setDataSo2(Float.parseFloat(row1.getCell(1).getStringCellValue()));
            row1.getCell(4).setCellType(CellType.STRING);
            one.setDataNox(Float.parseFloat(row1.getCell(4).getStringCellValue()));
            row1.getCell(6).setCellType(CellType.STRING);
            one.setDataO2(Float.parseFloat(row1.getCell(6).getStringCellValue()));
            row1.getCell(9).setCellType(CellType.STRING);
            one.setDataFc(Float.parseFloat(row1.getCell(9).getStringCellValue()));
            list.add(one);
            //
        }
        System.out.println("listSize:"+list.size());
        iCalcinationDesulfurizationDenitrationService.saveOrUpdateBatch(list);
        return ResultUtil.success("处理成功！");
    }
    
    
    /** 功能描述:
    *  处理浆液密度/
    * @return: 
    * @Author: 杨春科
    * @Date: 2021-06-04 9:35
    * @Description //TODO 
    */

    @ResponseBody
    @RequestMapping(method = RequestMethod.POST, value = "/importData")
    public Result<Object> importData(String url) throws Exception {
        File excelFile = new File(url);
        HSSFWorkbook wb = new HSSFWorkbook(new FileInputStream(excelFile));
        HSSFSheet sheet = wb.getSheetAt(0);
        HSSFRow row = sheet.getRow(2);


        for (int i=1 ; i <sheet.getLastRowNum(); i++) {

            HSSFRow row1 = sheet.getRow(i);
            Date dateCellValue = row1.getCell(0).getDateCellValue();
            row1.getCell(0).setCellType(CellType.STRING);
            String time=row1.getCell(0).getStringCellValue();

            //浆液池高度
            row1.getCell(1).setCellType(CellType.STRING);
            row1.getCell(2).setCellType(CellType.STRING);
            String gaodu="";
            if(row1.getCell(3)==null){
                gaodu=null;
            }else{
                row1.getCell(3).setCellType(CellType.STRING);
                gaodu=row1.getCell(3).getStringCellValue();
            }

            List<Float> rundom = DataHandlerUtils.getRundom(Float.parseFloat(row1.getCell(1).getStringCellValue()), Float.parseFloat(row1.getCell(2).getStringCellValue()),gaodu);
            //装置1进口温度
            row1.getCell(4).setCellType(CellType.STRING);
            row1.getCell(5).setCellType(CellType.STRING);
            String jkwd1="";
            if(row1.getCell(6)!=null){
                row1.getCell(6).setCellType(CellType.STRING);
                jkwd1=row1.getCell(6).getStringCellValue();
            }
            List<Float> rundom1 = DataHandlerUtils.getRundom(Float.parseFloat(row1.getCell(4).getStringCellValue()), Float.parseFloat(row1.getCell(5).getStringCellValue()),jkwd1);
           //1#反应器出口烟温(max)

            row1.getCell(7).setCellType(CellType.STRING);
            row1.getCell(8).setCellType(CellType.STRING);
            String ckwd1="";
            if(row1.getCell(9)!=null){
                row1.getCell(9).setCellType(CellType.STRING);
                jkwd1=row1.getCell(9).getStringCellValue();
            }
            List<Float> rundom2 = DataHandlerUtils.getRundom(Float.parseFloat(row1.getCell(7).getStringCellValue()), Float.parseFloat(row1.getCell(8).getStringCellValue()),ckwd1);
            //2#装置进口温度
            row1.getCell(10).setCellType(CellType.STRING);
            row1.getCell(11).setCellType(CellType.STRING);
            String jkwd2="";
            if(row1.getCell(12)!=null){
                row1.getCell(12).setCellType(CellType.STRING);
                jkwd2=row1.getCell(12).getStringCellValue();
            }
            List<Float> rundom3 = DataHandlerUtils.getRundom(Float.parseFloat(row1.getCell(10).getStringCellValue()), Float.parseFloat(row1.getCell(11).getStringCellValue()),jkwd2);
            //2#反应器出口烟温(max)
            row1.getCell(13).setCellType(CellType.STRING);
            row1.getCell(14).setCellType(CellType.STRING);
            String ckwd2="";
            if(row1.getCell(15)!=null){
                row1.getCell(15).setCellType(CellType.STRING);
                jkwd2=row1.getCell(15).getStringCellValue();
            }
            List<Float> rundom4 = DataHandlerUtils.getRundom(Float.parseFloat(row1.getCell(13).getStringCellValue()), Float.parseFloat(row1.getCell(14).getStringCellValue()),ckwd2);
            List<CalcinationDesulfurizationDenitration> list=new ArrayList<CalcinationDesulfurizationDenitration>(12);
            System.out.println("size:"+rundom.size());
           for(int j=0;j<rundom.size();j++){
               System.out.println("J:"+j);
               CalcinationDesulfurizationDenitration denitration=new CalcinationDesulfurizationDenitration();
               denitration.setId(String.valueOf(SnowFlakeUtil.getFlowIdInstance().nextId()) );
               denitration.setTime(DataHandlerUtils.dateAddHandler11(dateCellValue,j));
               denitration.setDsjhTllt(rundom.get(j));
               denitration.setDsjhT111(rundom1.get(j));
               denitration.setDsjhT121(rundom2.get(j));
               denitration.setDsjhT112(rundom3.get(j));
               denitration.setDsjhT122(rundom4.get(j));
               list.add(denitration);
           }
            iCalcinationDesulfurizationDenitrationService.saveBatch(list);
        }
        return ResultUtil.success("处理成功！");
    }


    /** 功能描述:
    *处理收录数据 纸质Paper
    * @return:
    * @Author: 杨春科
    * @Date: 2021-06-06 8:59
    * @Description //TODO
    */
    @ResponseBody
    @RequestMapping(method = RequestMethod.POST, value = "/importPaperData")
    public Result<Object> importPaperData(String url) throws Exception {
        File excelFile = new File(url);
        HSSFWorkbook wb = new HSSFWorkbook(new FileInputStream(excelFile));
        HSSFSheet sheet = wb.getSheetAt(0);
        HSSFRow row = sheet.getRow(2);

        List<CalcinationDesulfurizationDenitration> list=new ArrayList<CalcinationDesulfurizationDenitration>(800);
        for (int i=1 ; i <sheet.getLastRowNum(); i++) {


            HSSFRow row1 = sheet.getRow(i);
            if(row1.getCell(0)==null){
                break;
            }
            Date dateCellValue = row1.getCell(0).getDateCellValue();
            System.out.println(dateCellValue);
            row1.getCell(0).setCellType(CellType.STRING);
            String time=row1.getCell(0).getStringCellValue();
            CalcinationDesulfurizationDenitration denitration=new CalcinationDesulfurizationDenitration();
            denitration.setId(String.valueOf(SnowFlakeUtil.getFlowIdInstance().nextId()));
            denitration.setTime(dateCellValue.toInstant()
                    .atZone(ZoneId.systemDefault())
                    .toLocalDateTime());
            row1.getCell(1).setCellType(CellType.STRING);
            denitration.setDsjhTllt(Float.parseFloat(row1.getCell(1).getStringCellValue()));
            row1.getCell(2).setCellType(CellType.STRING);
            denitration.setDsjhT111(Float.parseFloat(row1.getCell(2).getStringCellValue()));
            row1.getCell(3).setCellType(CellType.STRING);
            denitration.setDsjhT121(DataHandlerUtils.dataHandler(Float.parseFloat(row1.getCell(3).getStringCellValue())));
            row1.getCell(4).setCellType(CellType.STRING);
            denitration.setDsjhT112(Float.parseFloat(row1.getCell(4).getStringCellValue()));
            row1.getCell(5).setCellType(CellType.STRING);
            denitration.setDsjhT122(DataHandlerUtils.dataHandler(Float.parseFloat(row1.getCell(5).getStringCellValue())));
           //1#装置进口压力
            row1.getCell(8).setCellType(CellType.STRING);
            denitration.setDsjhP111(Float.parseFloat(row1.getCell(8).getStringCellValue()));
            //2#装置进口压力
            row1.getCell(9).setCellType(CellType.STRING);
            denitration.setDsjhP121(Float.parseFloat(row1.getCell(9).getStringCellValue()));
            //1#装置进口压力
            row1.getCell(10).setCellType(CellType.STRING);
            denitration.setDsjhP111(Float.parseFloat(row1.getCell(10).getStringCellValue()));
            //2#装置进口压力
            row1.getCell(11).setCellType(CellType.STRING);
            denitration.setDsjhP121(Float.parseFloat(row1.getCell(11).getStringCellValue()));
            list.add(denitration);
        }
        iCalcinationDesulfurizationDenitrationService.saveBatch(list);
        return ResultUtil.success("处理成功！");
    }
    /** 功能描述:
    *  将网上拔下来的数据与手工录入数据合并
    * @return:
    * @Author: 杨春科
    * @Date: 2021-06-10 8:52
    * @Description //TODO
    */
    @ResponseBody
    @RequestMapping(method = RequestMethod.POST, value = "/mergeData")
    public Result<Object> mergeData(String url) throws Exception {
        HktsTailGas gas=new HktsTailGas();
        List<HktsTailGas> hktsTailGases = iHktsTailGasService.selectList(gas);
        for(int i=0;i<hktsTailGases.size();i++){
            QueryWrapper<CalcinationDesulfurizationDenitration> query = Wrappers.query();
            query.eq(CalcinationDesulfurizationDenitration.TIME,hktsTailGases.get(i).getTime());
            CalcinationDesulfurizationDenitration denitration = iCalcinationDesulfurizationDenitrationService.getOne(query);
            if(denitration==null){
                continue;
            }
            denitration.setDataFc(hktsTailGases.get(i).getA34013Avg());
            denitration.setDataSo2(hktsTailGases.get(i).getA21026Avg());
            denitration.setDataO2(hktsTailGases.get(i).getA19001Avg());
            denitration.setDataNox(hktsTailGases.get(i).getA21002Avg());
            iCalcinationDesulfurizationDenitrationService.updateById(denitration);
        }
        return ResultUtil.success("处理成功！");
    }
   /** 功能描述:
   *数据分割
   * @return: 
   * @Author: 杨春科
   * @Date: 2021-06-10 9:41
   * @Description //TODO 
   */
   @ResponseBody
   @RequestMapping(method = RequestMethod.POST, value = "/divisionData")
   @Transactional
   public Result<Object> divisionData() throws Exception {
       CalcinationDesulfurizationDenitration  Denitration=new CalcinationDesulfurizationDenitration();
       List<CalcinationDesulfurizationDenitration> calcinationDesulfurizationDenitrations = iCalcinationDesulfurizationDenitrationService.selectList(Denitration);
       iCalcinationDesulfurizationDenitrationService.deleteAll();
       for(int i=0;i<calcinationDesulfurizationDenitrations.size();i++){
               if (i != calcinationDesulfurizationDenitrations.size() - 1) {
                   LocalDateTime time = calcinationDesulfurizationDenitrations.get(i).getTime();

                   System.out.println(time);
                   Date date = Date.from( time.atZone( ZoneId.systemDefault()).toInstant());
                   List<LocalDateTime> allHourse = DataHandlerUtils.getAllHourse(date);
                   List<CalcinationDesulfurizationDenitration> list =new ArrayList<>();
                   //浆液池高度
                   List<Float> dataTllt = BezierPoint.getData(calcinationDesulfurizationDenitrations.get(i).getDsjhTllt(), calcinationDesulfurizationDenitrations.get(i + 1).getDsjhTllt());
                   //1#装置进口压力
                   List<Float> dataTll1 = BezierPoint.getData(calcinationDesulfurizationDenitrations.get(i).getDsjhT111(), calcinationDesulfurizationDenitrations.get(i + 1).getDsjhT111());
                    //2#装置进口温度
                   List<Float> dataTl21 = BezierPoint.getData(calcinationDesulfurizationDenitrations.get(i).getDsjhT121(), calcinationDesulfurizationDenitrations.get(i + 1).getDsjhT121());
                   //SO2实测浓度(二氧化硫)
                   List<Float> dataso2 = BezierPoint.getData(calcinationDesulfurizationDenitrations.get(i).getDataSo2(), calcinationDesulfurizationDenitrations.get(i + 1).getDataSo2());
                  //nox
                   List<Float> datanox = BezierPoint.getData(calcinationDesulfurizationDenitrations.get(i).getDataNox(), calcinationDesulfurizationDenitrations.get(i + 1).getDataNox());
                  //O2平均值(氧含量)
                   List<Float> datao2 = BezierPoint.getData(calcinationDesulfurizationDenitrations.get(i).getDataO2(), calcinationDesulfurizationDenitrations.get(i + 1).getDataO2());
                  //fc
                   List<Float> dataofc = BezierPoint.getData(calcinationDesulfurizationDenitrations.get(i).getDataFc(), calcinationDesulfurizationDenitrations.get(i + 1).getDataFc());
                  //t112
                   List<Float> data112 = BezierPoint.getData(calcinationDesulfurizationDenitrations.get(i).getDsjhT112(), calcinationDesulfurizationDenitrations.get(i + 1).getDsjhT112());
                   //t122
                   List<Float> data122 = BezierPoint.getData(calcinationDesulfurizationDenitrations.get(i).getDsjhT122(), calcinationDesulfurizationDenitrations.get(i + 1).getDsjhT122());
                  for(int j=0;j<allHourse.size();j++){
                      if(j==0){
                          calcinationDesulfurizationDenitrations.get(i).setId(String.valueOf(SnowFlakeUtil.getFlowIdInstance().nextId()));
                          list.add(calcinationDesulfurizationDenitrations.get(i));
                      }else{
                          CalcinationDesulfurizationDenitration  denitration=new CalcinationDesulfurizationDenitration();
                          denitration.setId(String.valueOf(SnowFlakeUtil.getFlowIdInstance().nextId()) );
                          denitration.setTime(allHourse.get(j));
                          denitration.setDataNox(datanox.get(j));
                          denitration.setDataO2(datao2.get(j));
                          denitration.setDataSo2(dataso2.get(j));
                          denitration.setDataFc(dataofc.get(j));
                          denitration.setDsjhT112(data112.get(j));
                          denitration.setDsjhT111(dataTll1.get(j));
                          denitration.setDsjhT121(dataTl21.get(j));
                          denitration.setDsjhT112(data112.get(j));
                          denitration.setDsjhTllt(dataTllt.get(j));
                          denitration.setDsjhT122(data122.get(j));
                          list.add(denitration);
                      }
                  }
                   iCalcinationDesulfurizationDenitrationService.saveBatch(list);
               }else{
                   calcinationDesulfurizationDenitrations.get(i).setId(String.valueOf(SnowFlakeUtil.getFlowIdInstance().nextId()) );
                   iCalcinationDesulfurizationDenitrationService.save(calcinationDesulfurizationDenitrations.get(i));
               }
       }
       return ResultUtil.success("处理成功！");
   }
      /** 功能描述:
      *根据时间将数据补全 所有值全为0
      * @return:
      * @Author: 杨春科
      * @Date: 2021-06-17 10:49
      * @Description //TODO
      */
      @ResponseBody
      @RequestMapping(method = RequestMethod.POST, value = "/addData")
      @Transactional
      public Result<Object> addData(String beginDate,Integer num) throws ParseException {
          List<LocalDateTime> allMinites = DataHandlerUtils.getAllMinites(beginDate, num);
          List<CalcinationDesulfurizationDenitration> calcinationDesulfurizationDenitrations =new ArrayList<CalcinationDesulfurizationDenitration>();
          for(int i=0;i<allMinites.size();i++){
              CalcinationDesulfurizationDenitration den=new CalcinationDesulfurizationDenitration();
              den.setId(String.valueOf(SnowFlakeUtil.getFlowIdInstance().nextId()));
              den.setTime(allMinites.get(i));
              den.setDsjhTllt(0f);
              den.setDsjhT111(0f);
              den.setDsjhT121(0f);
              den.setDataSo2(0f);
              den.setDataNox(0f);
              den.setDataFc(0f);
              den.setDataO2(0f);
              den.setDsjhT111(0f);
              den.setDsjhT122(0f);
              den.setDsjhT122(0f);
              den.setDsjhT112(0f);
              calcinationDesulfurizationDenitrations.add(den);
          }
          iCalcinationDesulfurizationDenitrationService.saveBatch(calcinationDesulfurizationDenitrations);
          return ResultUtil.success("处理成功！");
      }
     /** 功能描述:
     *煅烧脱硫脱硝  519之前的数据处理
     * @return:
     * @Author: 杨春科
     * @Date: 2021-06-19 10:28
     * @Description //TODO
     */
    @ResponseBody
    @RequestMapping(method = RequestMethod.POST, value = "/handlerData519")
    @Transactional
    public Result<Object> handlerData519() throws ParseException {
        iCalcinationDesulfurizationDenitrationService.handlerData519();
        return ResultUtil.success("处理成功！");
    }

    /** 功能描述:
     *  遍历文件 将数据导入数据库
     * @return:
     * @Author: 杨春科
     * @Date: 2021-06-02 18:04
     * @Description //TODO
     */
    @PostMapping("/importByFiles")
    public Result<Object> importByFiles(String url) throws Exception {
        File file=new File(url);
        XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(file));
        XSSFSheet sheet = wb.getSheetAt(0);
        List<CalcinationDesulfurizationDenitration> list=new ArrayList<CalcinationDesulfurizationDenitration>(24);
        for (int i = 1; i < sheet.getPhysicalNumberOfRows(); i ++) {
            XSSFRow row1 = sheet.getRow(i);
            CalcinationDesulfurizationDenitration bleaching=new CalcinationDesulfurizationDenitration();
            bleaching.setId(String.valueOf(SnowFlakeUtil.getFlowIdInstance().nextId()));
            System.out.println(row1.getCell(0).getDateCellValue());
            Date dateCellValue = row1.getCell(0).getDateCellValue();
            if(dateCellValue==null){
                continue;
            }
            bleaching.setTime(dateCellValue.toInstant()
                    .atZone( ZoneId.systemDefault() )
                    .toLocalDateTime());
            //焙烧一电场二次电流
            bleaching.setDsjhTllt((float)row1.getCell(1).getNumericCellValue());
            //焙烧一电场二次电压
            bleaching.setDsjhTlmd((float)row1.getCell(2).getNumericCellValue());
            //焙烧二电场二次电流
            bleaching.setDsjhTlph((float)row1.getCell(3).getNumericCellValue());
            //焙烧二电场二次电压
            bleaching.setDsjhFt101((float)row1.getCell(4).getNumericCellValue());
            bleaching.setDsjhFt102((float)row1.getCell(5).getNumericCellValue());
            bleaching.setDsjhP111((float)row1.getCell(6).getNumericCellValue());
            bleaching.setDsjhP121((float)row1.getCell(7).getNumericCellValue());
            bleaching.setDsjhT111((float)row1.getCell(8).getNumericCellValue());
            bleaching.setDsjhT121((float)row1.getCell(9).getNumericCellValue());
            list.add(bleaching);
        }
        iCalcinationDesulfurizationDenitrationService.saveBatch(list);

        return ResultUtil.success("成功");
    }

    /** 功能描述:
     *  遍历文件 将数据导入数据库
     * @return:
     * @Author: 杨春科
     * @Date: 2021-06-02 18:04
     * @Description //TODO
     */
    @PostMapping("/importByFilesByTime")
    public Result<Object> importByFilesByTime(String url) throws Exception {
        File file=new File(url);
        HSSFWorkbook wb = new HSSFWorkbook(new FileInputStream(file));
        HSSFSheet sheet = wb.getSheetAt(0);
        List<CalcinationDesulfurizationDenitration> list=new ArrayList<CalcinationDesulfurizationDenitration>(24);
        for (int i = 2; i < sheet.getPhysicalNumberOfRows(); i ++) {
            HSSFRow row1 = sheet.getRow(i);
            SimpleDateFormat sfm = new SimpleDateFormat("yyyy-MM-ddHH:mm:ss");
            if(row1.getCell(0)==null){
                break;
            }
            System.out.println(row1.getCell(0).getStringCellValue());

            Date parse = sfm.parse(row1.getCell(0).getStringCellValue().trim());
            String stringCellValue = row1.getCell(0).getStringCellValue();
            CalcinationDesulfurizationDenitration dataByTime = iCalcinationDesulfurizationDenitrationService.getDataByTime(stringCellValue);

            if(dataByTime==null){
                continue;
            }

            dataByTime.setDataSo2((float)row1.getCell(4).getNumericCellValue());
            //焙烧一电场二次电压
            dataByTime.setDataNox((float)row1.getCell(6).getNumericCellValue());
            //焙烧二电场二次电流
            dataByTime.setDataO2((float)row1.getCell(10).getNumericCellValue());
            //焙烧二电场二次电压
            dataByTime.setDataFc((float)row1.getCell(2).getNumericCellValue());

            list.add(dataByTime);
        }
        iCalcinationDesulfurizationDenitrationService.saveOrUpdateBatch(list);

        return ResultUtil.success("成功");
    }
   /** 功能描述:
   *将时间差中没有的数据置为空
   * @return: 
   * @Author: 杨春科
   * @Date: 2021-06-20 10:55
   * @Description //TODO 
   */
   @PostMapping("/handlerTimeDifferenceData")
   @Transactional
   public Result<String> handlerTimeDifferenceData(String url) throws Exception {
       File file=new File(url);
       File[] files=file.listFiles();
       SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
       Date date;
       for(int i=0;i<files.length;i++){
           File nextfile=  files[i];
           System.out.println(nextfile.getName());
           HSSFWorkbook wb = new HSSFWorkbook(new FileInputStream(nextfile));
           HSSFSheet sheet = wb.getSheetAt(0);
           List<RoastingDesulphurizing> list=new ArrayList<RoastingDesulphurizing>(20000);
           for (int j=5;j  >= 5&&j<sheet.getLastRowNum()-5; j ++) {
               HSSFRow row1 = sheet.getRow(j);
               Date dateCellValue = row1.getCell(0).getDateCellValue();
               HSSFRow row2 = sheet.getRow(j+1);
               Date dateCellValue2 = row2.getCell(0).getDateCellValue();
               long between = (dateCellValue2.getTime() - dateCellValue .getTime())/1000;
               long min = between/60;
               format.format(dateCellValue);
               format.format(dateCellValue2);
               if(min>1){
                   iCalcinationDesulfurizationDenitrationService.handlerTimeDifferenceData(format.format(dateCellValue),format.format(dateCellValue2));
               }
           }
       }

       return ResultUtil.success("处理成功");
   }

    @ResponseBody
    @RequestMapping(method = RequestMethod.POST, value = "/updateFt")
    @Transactional
    public Result<Object> updateFt() throws ParseException {
        iCalcinationDesulfurizationDenitrationService.updateFtByTime();
        return ResultUtil.success("处理成功！");
    }

    @ResponseBody
    @RequestMapping(method = RequestMethod.POST, value = "/deleteFt")
    @Transactional
    public Result<Object> deleteFt() {
        iCalcinationDesulfurizationDenitrationService.deleteFtByTime();
        return ResultUtil.success("处理成功！");
    }

}

